﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;//引用正则表达式类的命名空间
using System.IO;//文件操作
using community.LanFen;


namespace community
{
    public partial class users : Form
    {
        int sign = 0;//保存标志，0：修改型保存  1：新增型保存
        int user_right;//用户权限 1为管理员，2为普通用户
        string imagePath;//相片存储路径

        public string strSql;
        string strConn = SQL.conStr;

        SqlConnection conn;
        SqlCommand com;
        SqlDataAdapter da;
       
        public users()
        {
            InitializeComponent();
        }

        protected override bool ProcessDialogKey(Keys keyData)//按enter键时，光标跳到下一个控件输入
        {
            if (keyData == Keys.Enter)//&& !(ActiveControl is Button))
            {//碰到Button时不跳转到下一个控件，而是执行Button的单击事件
                keyData = Keys.Tab;
            }
            return base.ProcessDialogKey(keyData);
        }

        private void show_dataGridWiew()
        {
            strSql = "select EMPID_CHR as 职工工号,LASTNAME_VCHR as 职工姓名 from  T_BSE_EMPLOYEE";

            using (conn = new SqlConnection(strConn))
            {
            da = new SqlDataAdapter(strSql, conn);

            DataSet ds = new DataSet();
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int count = da.Fill(ds, "T_BSE_EMPLOYEE");
            //conn.Close();
            dataGridView1.DataSource = ds.Tables["T_BSE_EMPLOYEE"];
            }
            
        }

        private void users_Load(object sender, EventArgs e)
        {//显示用户表的数据

            show_dataGridWiew();
            reade_data();
        }

        //读取信息到信息栏
        private void reade_data()
        {
            string code = Convert.ToString(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value).Trim();
            string name = Convert.ToString(dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value).Trim();

            //MessageBox.Show(name );
            strSql = "select EMPID_CHR ,LASTNAME_VCHR,DATEDIFF(YYYY, BIRTHDATE_DAT, getdate()) as age ,SEX_CHR,BIRTHDATE_DAT ,";
            strSql = strSql + "DEPT_NAME_VCHR,EMPIDCARD_CHR,TECHNICALLEVEL_CHR,BEGINDATE_DAT,EDUCATIONALLEVEL_CHR,MARITALSTATUS_CHR,";
            strSql = strSql + "PSW_CHR,OFFICEPHONE_VCHR,HOMEPHONE_VCHR,MOBILE_VCHR,OFFICEADDRESS_VCHR,OFFICEZIP_CHR,HOMEADDRESS_VCHR,HOMEZIP_CHR,EMAIL_VCHR,QQ_VCHR,MSN_VCHR,RIGHT_INT ,IMAGE_CHR";
            strSql = strSql + " from  T_BSE_EMPLOYEE where EMPID_CHR='" + code + "'";

            using (conn = new SqlConnection(strConn))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                com = new SqlCommand(strSql, conn);

                try
                {
                    SqlDataReader rdr = com.ExecuteReader();

                    while (rdr.Read())
                    {
                        name_box.Text = rdr["LASTNAME_VCHR"].ToString();
                        code_box.Text = rdr["EMPID_CHR"].ToString();
                        age_box.Text = rdr["age"].ToString();
                        sex_box.Text = rdr["SEX_CHR"].ToString();
                        dateTimePicker1.Value = Convert.ToDateTime(rdr["BIRTHDATE_DAT"]);
                        dept_box.Text = rdr["DEPT_NAME_VCHR"].ToString();
                        ID_box.Text = rdr["EMPIDCARD_CHR"].ToString();//身份证号
                        level_box.Text = rdr["TECHNICALLEVEL_CHR"].ToString();

                        dateTimePicker2.Value = Convert.ToDateTime(rdr["BEGINDATE_DAT"]);
                        edu_box.Text = rdr["EDUCATIONALLEVEL_CHR"].ToString();
                        marrige_box.Text = rdr["MARITALSTATUS_CHR"].ToString();

                        pwd_box.Text = rdr["PSW_CHR"].ToString();
                        work_phone_box.Text = rdr["OFFICEPHONE_VCHR"].ToString();
                        family_phone_box.Text = rdr["HOMEPHONE_VCHR"].ToString();
                        person_phone_box.Text = rdr["MOBILE_VCHR"].ToString();
                        work_address_box.Text = rdr["OFFICEADDRESS_VCHR"].ToString();
                        work_post_box.Text = rdr["OFFICEZIP_CHR"].ToString();
                        family_address_box.Text = rdr["HOMEADDRESS_VCHR"].ToString();
                        family_post_box.Text = rdr["HOMEZIP_CHR"].ToString();
                        email_box.Text = rdr["EMAIL_VCHR"].ToString();
                        QQ.Text = rdr["QQ_VCHR"].ToString();
                        MSN.Text = rdr["MSN_VCHR"].ToString();
                        imagePath  = rdr["IMAGE_CHR"].ToString();

                        
                        if ((Convert.ToInt32 (rdr["RIGHT_INT"])==1))
                        {
                            right_box.Text = "管理员";
                        }else
                            right_box.Text = "普通用户";



                        
                        //显示图片
                        if (imagePath!= "")
                        {
                            string strImage = AppDomain.CurrentDomain.BaseDirectory + imagePath ;

                            if (File.Exists(strImage))//如果是文件的话
                            {  
                                picture.Image = Image.FromFile(strImage); //经常报错

                            }

                        }
                        else
                            picture.Image = null;

                    }

                    rdr.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

                conn.Close();
            }
        }

        //点击记录，把数据读取到右侧的职工信息表中。
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

            reade_data();

            code_box.Enabled = false;

        }

        
        //查找功能
        private void find_button_Click(object sender, EventArgs e)
        {

            string conditions1=find_code.Text.Trim();
            string  conditions2=find_name.Text.Trim();
            string conditions="";//存放查询条件

            if (( conditions1 == "") && (conditions2 == ""))
            {
                MessageBox.Show("请输入查询条件！");
                return;
            }

            if ((conditions1 != "") && (conditions2 == ""))
            {
                conditions = "where EMPID_CHR like  '%"+ conditions1+"%'" ;
            }

            if ((conditions1 == "") && (conditions2 != ""))
            {
                conditions = "where LASTNAME_VCHR like  '%" + conditions2 + "%'";
            }

            if ((conditions1 != "") && (conditions2 != ""))
            {
                conditions = "where EMPID_CHR like  '%" + conditions1 + "%'" + "and LASTNAME_VCHR like '%"+conditions2+"%'";
            }


            strSql = "select EMPID_CHR as 职工工号,LASTNAME_VCHR as 职工姓名 from  T_BSE_EMPLOYEE  ";
            strSql += conditions;
            //MessageBox.Show(strSql );
            try
            {
                using (conn = new SqlConnection(strConn))
                {
                    da = new SqlDataAdapter(strSql, conn);

                    DataSet ds = new DataSet();
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    int count = da.Fill(ds, "T_BSE_EMPLOYEE");
                    //conn.Close();
                    dataGridView1.DataSource = ds.Tables["T_BSE_EMPLOYEE"];
                }
            }catch (Exception ex)
            {
                MessageBox.Show(ex.ToString ());
            
            }
            reade_data();
        }

        //导入相片
        private void photo_button_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string oldName = openFileDialog1.FileName;
                string[] splitName = oldName.Split('.');
                string ext = splitName[splitName.Length - 1];
                imagePath = "/Image/" + DateTime.Now.ToString("yyyyMMddhhmmss") + "." + ext;
                string newName = AppDomain.CurrentDomain.BaseDirectory + imagePath;
                File.Copy(oldName, newName, true);
                Image img = Image.FromFile(newName);
                picture.Image = img;
            }
        }





        //清空了之后，在保存按钮保存数据到数据库
        private void 新增ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            name_box.Clear();
            code_box.Clear();
            dept_box.Clear();
            //sex_box.Text = "";
            age_box.Clear();
            ID_box.Clear();
            level_box.Clear();
            //edu_box.Text = "";
            //marrige_box.Text = "";
            pwd_box.Clear();
            work_phone_box.Clear();
            family_phone_box.Clear();
            person_phone_box.Clear();
            work_address_box.Clear();
            work_post_box.Clear();
            family_address_box.Clear();
            family_post_box.Clear();
            QQ.Clear();
            MSN.Clear();
            email_box.Clear();

            imagePath = "";
            picture.Image = null;

            //保存标志改为新增型；
            sign  = 1;

            pwd_box.Enabled = true;
            age_box.Enabled = false;
            code_box.Enabled = true;
        }

        //删除指定行
        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("确定删除改用户吗？", "删除提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
                return;


            string code = Convert.ToString(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value).Trim();
            strSql = "delete from T_BSE_EMPLOYEE where EMPID_CHR='" + code + "'";

            conn = new SqlConnection(strConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            com = new SqlCommand(strSql, conn);

            int i;

            try
            {
                i = com.ExecuteNonQuery();
                if (i == 1)
                    MessageBox.Show("恭喜！删除成功！");
                else
                {

                    MessageBox.Show("删除失败！");
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.ToString());
            }
            conn.Close();

            show_dataGridWiew();
        }


        private void 保存ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (sign == 1)//新增型保存操作
            {
                code_box.Enabled = true;
                //判断是否为空
                if ((name_box.Text.Trim() == "") || (code_box.Text.Trim() == ""))
                {
                    MessageBox.Show("职工姓名和职工工号不能为空！");
                    return;
                }

                //判断是否与主键重复
                using (conn = new SqlConnection(strConn))
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    string str2 = "select count( EMPID_CHR )  as 'num' from  T_BSE_EMPLOYEE where EMPID_CHR='" + code_box.Text.Trim() + "'";//获取职工号
                    int numInDb = 0;
                    //获取职工号
                    com = new SqlCommand(str2, conn);
                    SqlDataReader rdr = com.ExecuteReader();
                    while (rdr.Read())
                    {

                        numInDb = Convert.ToInt32(rdr["num"]);
                        //MessageBox.Show(numInDb.ToString () );
                    }
                    rdr.Close();
                    if (numInDb != 0)
                    {
                        MessageBox.Show("职工号已存在，请重新编号！");
                        sign = 0;
                        conn.Close();
                        return;
                    }



                    strSql = "insert into T_BSE_EMPLOYEE ( ";
                    strSql += "EMPID_CHR ,LASTNAME_VCHR,SEX_CHR,BIRTHDATE_DAT ,DEPT_NAME_VCHR,EMPIDCARD_CHR,TECHNICALLEVEL_CHR,";
                    strSql += "BEGINDATE_DAT,EDUCATIONALLEVEL_CHR,MARITALSTATUS_CHR,PSW_CHR,OFFICEPHONE_VCHR,HOMEPHONE_VCHR,MOBILE_VCHR,";
                    strSql += "OFFICEADDRESS_VCHR,OFFICEZIP_CHR,HOMEADDRESS_VCHR,HOMEZIP_CHR,EMAIL_VCHR,QQ_VCHR,MSN_VCHR,RIGHT_INT,IMAGE_CHR) values ('";

                    strSql += code_box.Text.Trim() + "','" + name_box.Text.Trim() + "','" + sex_box.Text.Trim() + "','" + dateTimePicker1.Value + "','" + dept_box.Text.Trim() + "','" + ID_box.Text.Trim() + "','" + level_box.Text.Trim();
                    strSql += "','" + dateTimePicker2.Value + "','" + edu_box.Text.Trim() + "','" + marrige_box.Text.Trim() + "','" + dataBase.encode(pwd_box.Text.Trim()) + "','" + work_phone_box.Text.Trim() + "','" + family_phone_box.Text.Trim() + "','" + person_phone_box.Text.Trim();
                    strSql += "','" + work_address_box.Text.Trim() + "','" + work_post_box.Text.Trim() + "','" + family_address_box.Text.Trim() + "','" + work_post_box.Text.Trim() + "','" + email_box.Text.Trim() + "','" + QQ.Text.Trim() + "','" + MSN.Text.Trim()+ "','" +user_right+ "','" +imagePath;
                    strSql += "')";

                    //Console.WriteLine(strSql );


                    com.CommandText = strSql;
                    int i;
                    try
                    {
                        i = com.ExecuteNonQuery();
                        if (i == 1)
                            MessageBox.Show("恭喜！保存成功！");
                        else
                        {

                            MessageBox.Show("保存失败！");
                        }
                    }
                    catch (Exception ex)
                    {

                        MessageBox.Show(ex.ToString());
                    }
                    conn.Close();


                    age_box.Enabled = true;
                }
                code_box.Enabled = true;
                //刷新数据
                show_dataGridWiew();

                pwd_box.Enabled = false;
                sign = 0;
            }

            else
            {
                int rightbool = 2;
                if (right_box.Text == "管理员")
                {
                    rightbool = 1;
                }
                else
                    rightbool = 2;

                //sql语句：
                strSql = "update T_BSE_EMPLOYEE set ";
                strSql += "EMPID_CHR ='" + code_box.Text.Trim() + "' ,LASTNAME_VCHR='" + name_box.Text.Trim() + "',SEX_CHR='" + sex_box.Text.Trim() + "',BIRTHDATE_DAT = '" + dateTimePicker1.Text.Trim() + "',DEPT_NAME_VCHR= '" + dept_box.Text.Trim() + "',";
                strSql += "EMPIDCARD_CHR='" + ID_box.Text.Trim() + "',TECHNICALLEVEL_CHR='" + level_box.Text.Trim() + "',BEGINDATE_DAT='" + dateTimePicker2.Value + "',EDUCATIONALLEVEL_CHR='" + edu_box.Text.Trim() + "',MARITALSTATUS_CHR='" + marrige_box.Text.Trim() + "',";
                strSql += "PSW_CHR='" + dataBase.encode ( pwd_box.Text.Trim() )+ "',OFFICEPHONE_VCHR='" + work_phone_box.Text.Trim() + "',HOMEPHONE_VCHR='" + family_phone_box.Text.Trim() + "',MOBILE_VCHR='" + person_phone_box.Text.Trim() + "',";
                strSql += "OFFICEADDRESS_VCHR='" + work_address_box.Text.Trim() + "',OFFICEZIP_CHR='" + work_post_box.Text.Trim() + "',HOMEADDRESS_VCHR='" + family_address_box.Text.Trim() + "',HOMEZIP_CHR='" + family_post_box.Text.Trim() + "',RIGHT_INT='" + rightbool + "',"; //right_box
                strSql += "EMAIL_VCHR='" + email_box.Text.Trim() + "',QQ_VCHR='" + QQ.Text.Trim() + "',MSN_VCHR='" + MSN.Text.Trim() + "',IMAGE_CHR='" + imagePath + "'";//IMAGE_CHR
                strSql += " where EMPID_CHR = '" + code_box.Text.Trim() + "'";

                //MessageBox.Show(strSql );

                using (conn = new SqlConnection(strConn))
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    com = new SqlCommand(strSql, conn);

                    int i;
                    try
                    {
                        i = com.ExecuteNonQuery();
                        if (i == 1)
                            MessageBox.Show("恭喜！修改成功！");
                        else
                        {

                            MessageBox.Show("保存失败！");
                        }
                    }
                    catch (Exception ex)
                    {

                        MessageBox.Show(ex.ToString());
                    }


                    conn.Close();
                }

                code_box.Enabled = false;
                //刷新：
                show_dataGridWiew();
            }
        }


        private void 重置密码ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if(MessageBox.Show ("确定重置密码吗？","重置密码提示",MessageBoxButtons.OKCancel,MessageBoxIcon.Question )!=DialogResult.OK )
                return ;


            string code = Convert.ToString(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value).Trim();

            using (SqlConnection conn = new SqlConnection (SQL.conStr))
            {
                string txt = "update T_BSE_EMPLOYEE set PSW_CHR = @pwd where EMPID_CHR=@uid";

                if (conn.State ==ConnectionState.Closed)
                    conn.Open ();
                SqlCommand com = conn.CreateCommand();
                com.CommandText = txt;
                com.Parameters.AddWithValue("@uid",code);
                com.Parameters.AddWithValue("@pwd",dataBase.encode ("888888"));
                try
                {
                    int i = com.ExecuteNonQuery();
                    if (i == 1)
                    {
                        MessageBox.Show("密码重置成功！初始密码为6个8.");

                    }
                    else
                    {
                        MessageBox.Show("重置失败！");
                        return;
                    }
                }catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString ());
                }
            }

        }

        //点击科室，出现下拉表格
        private void dept_box_Click(object sender, EventArgs e)
        {
            dataGridView_dept.Visible = true;
            try 
            {
           SqlConnection  cn = new SqlConnection(SQL.conStr);
            if (cn.State ==ConnectionState.Closed )
            {
                cn.Open();
            }

            string sqlStr = "select DEPTNAME_VCHR as 科室名称 ,DEPTID_CHR as ID from T_BSE_DEPARTMENT  ";
            SqlDataAdapter dt = new SqlDataAdapter(sqlStr, cn);
            DataSet ds = new DataSet();
            dt.Fill(ds);
            this.dataGridView_dept.DataSource = ds.Tables[0];
            cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void dataGridView_dept_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                dept_box.Text = dataGridView_dept.Rows[e.RowIndex].Cells[0].Value.ToString();
                if (dept_box.Text != null) dataGridView_dept.Visible = false;
            }
        }

        //用户权限选择
        private void right_box_Click(object sender, EventArgs e)
        {
            dataGridView_right.Visible = true;
            
           SqlConnection  cn = new SqlConnection(SQL.conStr);
            if (cn.State ==ConnectionState.Closed )
            {
                cn.Open();
            }

            string sqlStr = "select distinct RIGHTNAME_VCHR as 权限 ,  RIGHTID_INT as ID  from dbo.T_INF_RIGHTS  ";
            SqlDataAdapter dt = new SqlDataAdapter(sqlStr, cn);
            DataSet ds = new DataSet();
            dt.Fill(ds);
            this.dataGridView_right.DataSource = ds.Tables[0];
            cn.Close();
        }

        //权限表显示到textbox下方
        private void dataGridView_right_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                //string code = Convert.ToString(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value).Trim();
                //right_box.Text = Convert.ToString(dataGridView_right[1, dataGridView_right.CurrentCell.RowIndex].Value).Trim();
                right_box.Text = dataGridView_right.Rows[e.RowIndex].Cells[0].Value.ToString();
                user_right =Convert .ToInt32 ( dataGridView_right[1, dataGridView_right.CurrentCell.RowIndex].Value);
                if (right_box.Text != null) dataGridView_right.Visible = false;
            }
        
        }


        

    }
}
